﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using eVentasUpdater;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace WPFUpdater
{

    public class ExecuteScripts
    {
        bool individuals;
        public bool Individuals
        {
            get { return individuals; }
            set { individuals = value; }
        }

        bool group;
        public bool Group
        {
            get { return group; }
            set { group = value; }
        }

        string rutaIndividual;

        public string RutaIndividual
        {
            get { return rutaIndividual; }
            set { rutaIndividual = value; }
        }

        public ExecuteScripts()
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            try
            {
                string cnnString = AppConfs.CnnString;
                SqlConnection Connection = new SqlConnection(cnnString);
                SqlCommand cmd = new SqlCommand("SP_CMD_GET_EXECINDIVSCRIPTS", Connection);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                int res = da.Fill(dt);

                if (res > 0)
                {
                    this.Individuals = bool.Parse(dt.Rows[0][0].ToString());
                    this.RutaIndividual = dt.Rows[0][1].ToString();
                    this.Group = bool.Parse(dt.Rows[0][2].ToString());
                }
                else
                {
                    this.Group = false;
                    this.RutaIndividual = "";
                    this.Individuals = false;
                }

            }
            catch (Exception ex)
            {
                this.Group = false;
                this.RutaIndividual = "";
                this.Individuals = false;
                throw ex;
            }
        }

        public void upsertFlags(int individual, int group)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            string query = "", ind, grp;

            // si el valor es 2, entonces no debe actualizarse
            if (individual == 2) ind = "";
            else ind = "@INDIVIDUALFLAG=" + individual.ToString();

            if (group == 2) grp = "";
            else grp = "', @GROUPFLOAG = " + group.ToString();

            query = "SP_CMD_UPSERT_EXECSCRIPTS "
                + ind
                //+ ", @SCRIPTSPATH= '" + path
                + grp;

            try
            {
                string cnnString = AppConfs.CnnString;
                SqlConnection Connection = new SqlConnection(cnnString);
                SqlCommand cmd = new SqlCommand(query, Connection);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                int res = da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static Dictionary<string, string> ExecuteScript(string script)
        {
            string cnnString = AppConfs.CnnString;
            Dictionary<string, string> resultado = new Dictionary<string, string>();

            try
            {
                execSQL(cnnString, script);
                resultado.Add("OK", "Script " + script + " ejecutado");
            }
            catch (Exception ex)
            {
                resultado.Add("ERR", ex.Message);
            }
            return resultado;
        }//

        public static Dictionary<string, string> ExecuteScriptsFromPath()
        {
            string result = "";
            Dictionary<string, string> resultado = new Dictionary<string, string>();

            string cnnString = AppConfs.CnnString;
            string path = AppConfs.SourcePath;


            try
            {
                string[] listaScripts = ScriptsList(path);
                if (listaScripts.Length > 0)
                {
                    string nombre = "";
                    foreach (string script in listaScripts)
                    {
                        try
                        {
                            string[] n = script.Split('\\');
                            nombre = n[n.Length - 1];

                            Console.WriteLine("\n- Ejecutando Script: " + nombre);
                            execSQL(cnnString, script);
                            result += "Script ejecutado \n";
                        }
                        catch (Exception exe)
                        {
                            Console.WriteLine("\tNo se pudo completar la ejecución de " + nombre + ". " + exe.Message);
                            result += "\tNo se pudo completar la ejecución de " + nombre + ". " + exe.Message + "\n";
                        }

                    }//for each
                }
                else
                {
                    Console.WriteLine("\tNo se encontraron archivos en la ruta fuente \"" + path + "\"");
                    result += "\tNo se encontraron archivos en la ruta fuente \"" + path + "\" \n";
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                result += "Error en la ejecución de Scripts: " + ex.Message + "\n";
                resultado.Add("ERR", result);
            }
            resultado.Add("OK", result);

            return resultado;
        }//


        public static string[] ScriptsList(string path)
        {

            try
            {
                string[] arr;

                if (Directory.Exists(path))
                {
                    arr = Directory.GetFiles(path);
                }
                else arr = new string[0];

                return arr;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error al recuperar scripts.");
                throw new Exception(ex.Message);
            }


        }//

        public static void execSQL(string cnn, string scriptName)
        {
            string connStr = cnn;
            FileInfo file;
            string script = "";

            try
            {
                file = new FileInfo(scriptName); // (@"C:\Users\Inmeta\Downloads\test.sql");

                if (file.Extension != ".sql")
                {
                    Console.WriteLine("No se puede ejecutar el archivo " + file.Name + " ya que no es de tipo sql.");
                    return;
                }

                script = file.OpenText().ReadToEnd();
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);

            }

            IEnumerable<string> commandStrings = Regex.Split(script, "^\\s*GO\\s*$", RegexOptions.Multiline);

            SqlConnection Connection = new SqlConnection(connStr);
            int rowsAffected = 0;
            try
            {
                Connection.Open();
                foreach (string commandString in commandStrings)
                {
                    if (commandString.Trim() != "")
                    {
                        SqlCommand cmd = new SqlCommand(commandString, Connection);
                        rowsAffected = cmd.ExecuteNonQuery();
                    }
                }
                Connection.Close();

                Console.WriteLine("Script ejecutado!\tRows affected:" + rowsAffected);
            }
            catch (Exception ex)
            {
                throw new Exception("Error en la ejecución: " + ex.Message);
            }

            Connection.Dispose();
        }//end


    }// end class
}
